#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
DM到MySQL SQL转换工具
将达梦数据库的建表SQL语句转换为MySQL数据库的建表SQL语句
"""
import re
import os


def convert_dm_to_mysql(dm_sql_file, mysql_sql_file):
    """
    将达梦数据库SQL文件转换为MySQL数据库SQL文件
    :param dm_sql_file: 达梦SQL文件路径
    :param mysql_sql_file: MySQL SQL文件路径
    """
    try:
        # 读取达梦SQL文件
        with open(dm_sql_file, 'r', encoding='utf-8') as f:
            content = f.read()
        
        # 预处理：将文件内容按语句分割并去除注释
        statements = split_sql_statements(content)
        
        # 提取所有的表注释和列注释
        table_comments = {}
        column_comments = {}
        
        # 第一次遍历：收集所有注释
        for statement in statements:
            if statement.strip().upper().startswith('COMMENT ON TABLE'):
                match = re.search(r'COMMENT ON TABLE\s+"([^"]+)"\."([^"]+)"\s+IS\s+\'([^\']+)\'', statement, re.IGNORECASE)
                if match:
                    schema_name = match.group(1)
                    table_name = match.group(2).lower()
                    comment = match.group(3)
                    table_comments[table_name] = comment
            elif statement.strip().upper().startswith('COMMENT ON COLUMN'):
                match = re.search(r'COMMENT ON COLUMN\s+"([^"]+)"\."([^"]+)"\."([^"]+)"\s+IS\s+\'([^\']+)\'', statement, re.IGNORECASE)
                if match:
                    schema_name = match.group(1)
                    table_name = match.group(2).lower()
                    column_name = match.group(3).lower()
                    comment = match.group(4)
                    if table_name not in column_comments:
                        column_comments[table_name] = {}
                    column_comments[table_name][column_name] = comment
        
        mysql_statements = []
        
        # 第二次遍历：处理CREATE TABLE语句
        for statement in statements:
            if not statement.strip():
                continue
            
            # 处理CREATE TABLE语句
            if statement.strip().upper().startswith('CREATE TABLE'):
                mysql_statement = convert_create_table(statement, table_comments, column_comments)
                mysql_statements.append(mysql_statement)
        
        # 写入MySQL SQL文件
        with open(mysql_sql_file, 'w', encoding='utf-8') as f:
            f.write('\n\n'.join(mysql_statements))
        
        print(f"转换完成！MySQL SQL已保存至: {mysql_sql_file}")
        print(f"共转换了 {len(mysql_statements)} 条SQL语句")
        
    except Exception as e:
        print(f"转换过程中发生错误: {str(e)}")


def split_sql_statements(content):
    """
    将SQL文件内容按语句分割
    """
    # 首先去除行内注释
    content = re.sub(r'--.*?$', '', content, flags=re.MULTILINE)
    
    # 分割SQL语句（以分号为分隔符）
    statements = []
    current_statement = ''
    in_string = False
    in_comment = False
    
    for char in content:
        if char == '\'' and (not current_statement or current_statement[-1] != '\\'):
            in_string = not in_string
        elif char == '/' and len(current_statement) > 0 and current_statement[-1] == '*' and not in_string:
            in_comment = True
        elif char == '*' and len(current_statement) > 0 and current_statement[-1] == '/' and not in_string and in_comment:
            in_comment = False
            current_statement = current_statement[:-1]  # 移除 '*'
            continue
        
        if not in_comment:
            current_statement += char
            if char == ';' and not in_string:
                statements.append(current_statement)
                current_statement = ''
    
    # 处理最后一个没有分号结束的语句
    if current_statement.strip():
        statements.append(current_statement)
    
    return statements


def convert_create_table(statement, table_comments, column_comments):
    """
    转换CREATE TABLE语句
    """
    # 提取表名
    table_match = re.search(r'CREATE TABLE\s+"([^"]+)"\."([^"]+)"', statement, re.IGNORECASE)
    if not table_match:
        # 尝试匹配没有模式名的情况
        table_match = re.search(r'CREATE TABLE\s+"([^"]+)"', statement, re.IGNORECASE)
        if not table_match:
            # 尝试匹配没有引号的情况
            table_match = re.search(r'CREATE TABLE\s+(\w+)', statement, re.IGNORECASE)
            if not table_match:
                return statement
            table_name = table_match.group(1).lower()
        else:
            table_name = table_match.group(1).lower()
    else:
        table_name = table_match.group(2).lower()
        
    # MySQL关键字列表
    mysql_keywords = {
        'key', 'system', 'user', 'host', 'port', 'path', 'password', 'group', 
        'order', 'select', 'insert', 'update', 'delete', 'from', 'where', 
        'and', 'or', 'join', 'on', 'as', 'like', 'in', 'between', 'limit', 
        'having', 'group by', 'order by'
    }
    
    # 提取表结构部分
    structure_pattern = r'\((?:(?:"[^"]*"|\([^()]*\)|[^()"])*)\)'  # 处理括号嵌套的正则表达式
    structure_match = re.search(structure_pattern, statement, re.DOTALL)
    if not structure_match:
        return statement
    
    structure = structure_match.group(0)[1:-1].strip()  # 去掉外层括号
    
    # 使用更复杂的列定义分割方法，处理括号嵌套和引号
    columns = []
    bracket_count = 0
    current_col = ''
    in_quotes = False
    quote_char = None
    
    for char in structure:
        # 跟踪引号状态
        if char in ['"', "'", '`'] and (not quote_char or char == quote_char) and (not current_col or current_col[-1] != '\\'):
            in_quotes = not in_quotes
            quote_char = char if in_quotes else None
        
        # 跟踪括号状态
        if char == '(' and not in_quotes:
            bracket_count += 1
        elif char == ')' and not in_quotes:
            bracket_count -= 1
        
        # 分割列定义
        if char == ',' and bracket_count == 0 and not in_quotes:
            columns.append(current_col.strip())
            current_col = ''
        else:
            current_col += char
    
    # 添加最后一个列定义
    if current_col.strip():
        columns.append(current_col.strip())
    
    # 解析列定义和主键
    mysql_columns = []
    primary_keys = []
    
    # 处理每个列定义
    for col_def in columns:
        col_def = col_def.strip()
        if not col_def:
            continue
        
        # 检查是否包含PRIMARY KEY定义
        if 'PRIMARY KEY' in col_def.upper():
            # 匹配列级主键定义
            pk_col_match = re.search(r'"([^"]+)"\s+PRIMARY KEY', col_def, re.IGNORECASE)
            if pk_col_match:
                primary_keys.append(pk_col_match.group(1).lower())
            # 匹配表级主键定义
            pk_table_match = re.search(r'PRIMARY KEY\s*\(([^)]+)\)', col_def, re.IGNORECASE)
            if pk_table_match:
                pk_columns = re.findall(r'"([^"]+)"', pk_table_match.group(1))
                for pk_col in pk_columns:
                    primary_keys.append(pk_col.lower())
            continue
        
        # 检查是否包含列名（用双引号括起来）
        col_match = re.search(r'"([^"]+)"', col_def)
        if not col_match:
            continue
        
        col_name = col_match.group(1).lower()
        
        # 转换数据类型和属性
        # 处理IDENTITY，确保完整匹配
        col_def = re.sub(r'IDENTITY\s*\(\s*\d+\s*,\s*\d+\s*\)', 'AUTO_INCREMENT', col_def, flags=re.IGNORECASE)
        # 处理不完整的IDENTITY语法
        col_def = re.sub(r'IDENTITY\s*\(\s*\d+\s*,', 'AUTO_INCREMENT', col_def, flags=re.IGNORECASE)
        # 处理简化的IDENTITY
        col_def = re.sub(r'IDENTITY', 'AUTO_INCREMENT', col_def, flags=re.IGNORECASE)
        
        # 处理DATETIME(0)及其他精度
        col_def = re.sub(r'DATETIME\s*\(\s*\d+\s*\)', 'DATETIME', col_def, flags=re.IGNORECASE)
        # 处理VARCHAR2
        col_def = re.sub(r'VARCHAR2', 'VARCHAR', col_def, flags=re.IGNORECASE)
        # 处理NUMBER(22,0) -> INT
        col_def = re.sub(r'NUMBER\s*\(\s*\d+\s*,\s*0\s*\)', 'INT', col_def, flags=re.IGNORECASE)
        # 处理NUMBER(22,2) -> DECIMAL(22,2)
        col_def = re.sub(r'NUMBER\s*\(\s*(\d+)\s*,\s*(\d+)\s*\)', lambda m: f'DECIMAL({m.group(1)},{m.group(2)})', col_def, flags=re.IGNORECASE)
        # 处理NUMBER(22) -> INT
        col_def = re.sub(r'NUMBER\s*\(\s*\d+\s*\)', 'INT', col_def, flags=re.IGNORECASE)
        # 处理简化的NUMBER
        col_def = re.sub(r'NUMBER', 'INT', col_def, flags=re.IGNORECASE)
        # 处理INTEGER
        col_def = re.sub(r'INTEGER', 'INT', col_def, flags=re.IGNORECASE)
        # 处理TIMESTAMP(6)
        col_def = re.sub(r'TIMESTAMP\s*\(\s*\d+\s*\)', 'DATETIME', col_def, flags=re.IGNORECASE)
        # 处理CLOB -> TEXT
        col_def = re.sub(r'CLOB', 'TEXT', col_def, flags=re.IGNORECASE)
        # 处理BLOB -> LONGBLOB
        col_def = re.sub(r'BLOB', 'LONGBLOB', col_def, flags=re.IGNORECASE)
        # 移除多余的NOT关键字
        col_def = re.sub(r'\bNOT\s+NOT\b', 'NOT', col_def, flags=re.IGNORECASE)
        # 移除STORAGE子句
        col_def = re.sub(r'STORAGE\s*\(\s*ON\s+[^)]+\)', '', col_def, flags=re.IGNORECASE)
        # 移除TABLESPACE子句
        col_def = re.sub(r'TABLESPACE\s+[^\s,]+', '', col_def, flags=re.IGNORECASE)
        
        # 移除双引号
        col_def = re.sub(r'"([^"]+)"', lambda m: m.group(1).lower(), col_def)
        
        # 检查列名是否是MySQL关键字，如果是则添加反引号
        if col_name.lower() in mysql_keywords:
            # 重新构建列定义，确保列名被正确匹配和替换
            col_def = re.sub(r'^\s*'+re.escape(col_name)+r'\b', f'`{col_name}`', col_def, flags=re.IGNORECASE)
            
        # 添加列注释
        if table_name in column_comments and col_name in column_comments[table_name]:
            # 避免重复添加注释
            if 'COMMENT' not in col_def.upper():
                col_def += f" COMMENT '{column_comments[table_name][col_name]}'"
        
        # 确保列定义有效才添加
        if col_def.strip():
            mysql_columns.append(col_def)
    
    # 构建MySQL CREATE TABLE语句
    mysql_statement = f"CREATE TABLE {table_name} (\n"
    
    # 添加有效列定义
    valid_columns = []
    for col in mysql_columns:
        if col.strip() and col.strip() != ',':
            valid_columns.append(col)
    
    if valid_columns:
        mysql_statement += ',\n'.join([f'    {col}' for col in valid_columns])
    
    # 添加主键约束（如果尚未在列定义中包含）
    if primary_keys:
        # 检查是否已有主键约束
        has_pk = any('PRIMARY KEY' in col.upper() for col in valid_columns)
        if not has_pk:
            if valid_columns:
                mysql_statement += ',\n'
            # 检查主键列是否是关键字，如果是则添加反引号
            quoted_primary_keys = []
            for pk in primary_keys:
                if pk.lower() in mysql_keywords:
                    quoted_primary_keys.append(f'`{pk}`')
                else:
                    quoted_primary_keys.append(pk)
            primary_key_str = ', '.join(quoted_primary_keys)
            mysql_statement += f'    PRIMARY KEY ({primary_key_str})'
    
    mysql_statement += '\n)'
    
    # 添加表注释
    if table_name in table_comments:
        mysql_statement += f" COMMENT='{table_comments[table_name]}'"
    
    mysql_statement += ';'
    
    return mysql_statement


if __name__ == '__main__':
    # 源文件和目标文件路径
    dm_sql_path = 'dm.sql'
    mysql_sql_path = 'mysql.sql'
    
    # 检查源文件是否存在
    if not os.path.exists(dm_sql_path):
        print(f"错误：源文件 '{dm_sql_path}' 不存在！")
        exit(1)
    
    # 执行转换
    convert_dm_to_mysql(dm_sql_path, mysql_sql_path)